﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.SqlServerCe;
using System.IO;
using System.Xml;

namespace DeloUtils.Migra
{
    /// <summary>
    ///режим подключения к локальной базе данных
    ///</summary>
    enum TModeLocalDB
	{
	    Create, 
        Exist
	}

    /// <summary>
    ///выбор режима синхронизации данных
    ///</summary>
    enum TAnalys
    {
        onetoone, // попытка использовать тотже идентификатор что и xml-файле
        byname, // ищет искомое значение по именам
        bytable // Была составлена таблица соответсвия
    }

    /// <summary>
    ///класс для работы с локальной базой данных
    ///</summary>
    class LocalDBCompact
    {
        /// <summary>
        ///имя файла локальной базы данных
        ///</summary>
        public String Name;

        /// <summary>
        /// сгенерированая строка подключения
        ///</summary>
        public String ConnectionString;

        /// <summary>
        /// соединение с локальной базой данных
        ///</summary>
        public OleDbConnection Connection;

        /// <summary>
        /// соединение с рабочей базой данных
        ///</summary>
        public OleDbConnection SyncConnection;

        /// <summary>
        /// метод синхронизации данных
        ///</summary>
        public TAnalys analyz = TAnalys.onetoone;

        /// <summary>
        /// группа документов по умолчанию для входящих
        ///</summary>
        public string defaultRcIn = "";

        /// <summary>
        /// группа документов по умолчанию для писем граждан
        ///</summary>
        public string defaultRcLet = "";

        /// <summary>
        /// группа документов по умолчанию для исходящих
        ///</summary>
        public string defaultRcOut = "";

        /// <summary>
        /// группа документов в рабочей базе данных, в которой будем проводить поиск для синхронихзации
        ///</summary>
        public string topRcDocgroup = "0.";

        /// <summary>
        /// картотека по умолчанию
        ///</summary>
        public string topCard = "0.";

        /// <summary>
        /// Вершина справочника подразделений, в которой будем проводить поиск для синхронихзации
        ///</summary>
        public string topDepartment = "0.";

        /// <summary>
        /// Включение/отключение проверки кэша загруженных справочников при их синхронизации
        /// </summary>
        public bool checksynctable = true;

        /// <summary>
        /// Таблица соответсвий дополнительных реквизитов
        /// </summary>
        public Dictionary<string, string> ARRCVALUE = new Dictionary<string, string>();

        /// <summary>
        /// Вершина справочника подразделений, в которой будем проводить поиск для синхронихзации
        /// Вычисляется автоматически в зависимости от topDepartment
        ///</summary>
        private Int32 topDepartmentISN
        {
            get
            {
                Int32 pisn = 0;
                try
                {
                    string sql = "select ISN_NODE FROM " + sqlprefix + "DEPARTMENT WHERE (DUE = '" +
                        topDepartment + "')";
                    OleDbCommand cmd = new OleDbCommand(sql, SyncConnection);
                    pisn = Int32.Parse(cmd.ExecuteScalar().ToString()); // вряд будет указан в качестве родительского несуществующий каталог
                }
                catch
                {
                    return 0;
                }
                return pisn;
            }
        }

        /// <summary>
        /// Вершина справочника организаций, в которой будем проводить поиск для синхронихзации
        ///</summary>
        public string topOrganiz = "0.";

        /// <summary>
        /// Вершина справочника организаций, в которой будем проводить поиск для синхронихзации
        /// Вычисляется автоматически в зависимости от topOrganiz
        ///</summary>
        private Int32 topOrganizISN
        {
            get
            {
                Int32 pisn = 0;
                try
                {
                    string sql = "select ISN_NODE FROM " + sqlprefix + "ORGANIZ_CL WHERE (DUE = '" +
                        topOrganiz + "')";
                    OleDbCommand cmd = new OleDbCommand(sql, SyncConnection);
                    pisn = Int32.Parse(cmd.ExecuteScalar().ToString()); // вряд будет указан в качестве родительского несуществующий каталог
                }
                catch
                {
                    return 0;
                }
                return pisn;
            }
        }

        /// <summary>
        /// Вершина справочника рубрик, в которой будем проводить поиск для синхронихзации
        ///</summary>
        public string topRubric = "0.";

        /// <summary>
        /// Вершина справочника организаций, в которой будем проводить поиск для синхронихзации
        /// Вычисляется автоматически в зависимости от topOrganiz
        ///</summary>
        private Int32 topRubricISN
        {
            get
            {
                Int32 pisn = 0;
                try
                {
                    string sql = "select ISN_NODE FROM " + sqlprefix + "RUBRIC_CL WHERE (DUE = '" +
                        topRubric + "')";
                    OleDbCommand cmd = new OleDbCommand(sql, SyncConnection);
                    pisn = Int32.Parse(cmd.ExecuteScalar().ToString()); // вряд будет указан в качестве родительского несуществующий каталог
                }
                catch
                {
                    return 0;
                }
                return pisn;
            }
        }

        /// <summary>
        /// sql префикс для оракла
        ///</summary>
        public string sqlprefix = "";

        /// <summary>
        /// инициализация
        ///</summary>
        public LocalDBCompact(String Name, TModeLocalDB mode)
        {
            ConnectionString = "Data Source='" + Name + "';Locale Identifier=1049;";
            if (File.Exists(Name) && (mode == TModeLocalDB.Create))
            {
                File.Delete(Name);
            }

            if (mode == TModeLocalDB.Create)
            {
                SqlCeEngine eng = new SqlCeEngine(ConnectionString); // создаем файл локальной базы данных
                eng.CreateDatabase();
            }

            ConnectionString = "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;" + ConnectionString;
            Connection = new OleDbConnection(ConnectionString);
            Connection.Open();

            // создаются таблицы в локальной базе данных
            if (mode == TModeLocalDB.Create)
            {
                String sql = "CREATE TABLE PROTOCOL ( ";
                sql += "ID int IDENTITY (100,1) PRIMARY KEY, ";
                sql += "TNAME NVARCHAR(32), ";
                sql += "IMPORTTIME DATETIME, ";
                sql += "IMPORTISN BIGINT, ";
                sql += "IMPORTDUE NVARCHAR(48), ";
                sql += "EXPORTTIME DATETIME, ";
                sql += "EXPORTISN BIGINT, ";
                sql += "EXPORTDUE NVARCHAR(48)) ";

                OleDbCommand cmd = new OleDbCommand(sql, Connection);
                cmd.ExecuteNonQuery();
                sql = "CREATE UNIQUE INDEX IND_PROTOCOL_TNAME_EXPORTISN ON PROTOCOL (TNAME, EXPORTISN)";
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
                sql = "CREATE INDEX IND_PROTOCOL_TNAME_IMPORTISN ON PROTOCOL (TNAME, IMPORTISN)";
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
                sql = "CREATE INDEX IND_PROTOCOL_IMPORTISN ON PROTOCOL (IMPORTISN)";
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
                sql = "CREATE INDEX IND_PROTOCOL_IMPORTDUE ON PROTOCOL (IMPORTDUE)";
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
                sql = "CREATE INDEX IND_PROTOCOL_EXPORTISN ON PROTOCOL (EXPORTISN)";
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
                sql = "CREATE INDEX IND_PROTOCOL_EXPORTDUE ON PROTOCOL (EXPORTDUE)";
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "CREATE TABLE DEPARTMENT ( ";
                sql += "ISN_NODE INT PRIMARY KEY, ";
                sql += "ISN_HIGH_NODE int, ";
                sql += "DUE NVARCHAR(48), ";
                sql += "IS_NODE INT, ";
                sql += "CLASSIF_NAME NVARCHAR(255), ";
                sql += "SURNAME NVARCHAR(64), ";
                sql += "DUTY NVARCHAR(255), ";
                sql += "DELETED INT, ";
                sql += "CARD_FLAG INT, ";
                sql += "CARD_NAME NVARCHAR(64),";
                sql += "IMPORTISN int, ";
                sql += "IMPORTDUE NVARCHAR(48)) ";
                cmd = new OleDbCommand(sql, Connection);
                cmd.ExecuteNonQuery();

                sql = "CREATE TABLE DOCGROUP ( ";
                sql += "ISN_NODE INT PRIMARY KEY, ";
                sql += "ISN_HIGH_NODE int, ";
                sql += "DUE NVARCHAR(48), ";
                sql += "IS_NODE INT, ";
                sql += "CLASSIF_NAME NVARCHAR(255), ";
                sql += "DELETED INT, ";
                sql += "IMPORTISN int, ";
                sql += "IMPORTDUE NVARCHAR(48)) ";
                cmd = new OleDbCommand(sql, Connection);
                cmd.ExecuteNonQuery();

                sql = "CREATE TABLE SECURITY ( ";
                sql += "SECURLEVEL INT PRIMARY KEY, ";
                sql += "GRIF_NAME NVARCHAR(64), ";
                sql += "IMPORTISN int) ";
                cmd = new OleDbCommand(sql, Connection);
                cmd.ExecuteNonQuery();

                sql = "CREATE TABLE RUBRIC_CL ( "
                    + "DUE NVARCHAR(48),ISN_NODE INT PRIMARY KEY,ISN_HIGH_NODE int,LAYER int,IS_NODE int,CLASSIF_NAME NVARCHAR(255)"
                    + ",DELETED INT,RUBRIC_CODE NVARCHAR(48), IMPORTISN int, IMPORTDUE NVARCHAR(48))";
                cmd = new OleDbCommand(sql, Connection);
                cmd.ExecuteNonQuery();

                sql = "CREATE TABLE DELIVERY_CL ( "
                     + "ISN_LCLASSIF INT PRIMARY KEY,CLASSIF_NAME NVARCHAR(255),PROTECTED INT,DELETED INT, IMPORTISN int)";

                cmd = new OleDbCommand(sql, Connection);
                cmd.ExecuteNonQuery();

                sql = "CREATE TABLE CABINET ( "
                     + "ISN_CABINET INT PRIMARY KEY,DUE NVARCHAR(48),CABINET_NAME NVARCHAR(255), IMPORTISN int)";

                cmd = new OleDbCommand(sql, Connection);
                cmd.ExecuteNonQuery();

                sql = "CREATE TABLE ORGANIZ_CL ( "
                     + "DUE NVARCHAR(48),ISN_NODE INT PRIMARY KEY,ISN_HIGH_NODE INT,IS_NODE INT,CLASSIF_NAME NVARCHAR(255)"
                     + ",PROTECTED INT,DELETED INT,ZIPCODE NVARCHAR(12),ADDRESS NVARCHAR(255),OKPO NVARCHAR(8)"
                     + ",INN NVARCHAR(64),ISN_REGION INT,OKONH NVARCHAR(16),LAW_ADRESS NVARCHAR(255), IMPORTISN int, IMPORTDUE NVARCHAR(48))";

                cmd = new OleDbCommand(sql, Connection);
                cmd.ExecuteNonQuery();

                sql = "CREATE TABLE CITIZEN ( "
                     + "ISN_CITIZEN INT PRIMARY KEY,CITIZEN_SURNAME NVARCHAR(64),CITIZEN_CITY NVARCHAR(255)"
                     + ",ZIPCODE NVARCHAR(12),CITIZEN_ADDR NVARCHAR(255),DELETED INT,PROTECTED INT, IMPORTISN int)";

                cmd = new OleDbCommand(sql, Connection);
                cmd.ExecuteNonQuery();

                sql = "CREATE TABLE PROT ( "
                    + "TABLE_ID NVARCHAR(48),OPER_ID NVARCHAR(48),SUBOPER_ID NVARCHAR(48),OPER_DESCRIBE NVARCHAR(48),REF_ISN INT"
                    + ",TIME_STAMP NVARCHAR(48),USER_ISN INT,OPER_COMMENT NVARCHAR(255))";

                cmd = new OleDbCommand(sql, Connection);
                cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// метод для сохранения протокола о выгруженых документов. можно использовать и для выгрузки других таблиц
        ///</summary>
        public void Save(Data data)
        {
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = Connection;
            String tname = "null";
            if (data.Name != string.Empty)
            {
                tname = "'" + data.Name + "'";
            }
            String ImportTime = "null";
            if (data.ImportTime != DateTime.MinValue)
            {
                ImportTime = "'" + data.ImportTime.ToString("yyyy-MM-dd hh:mm:ss") + "'";
            }

            if (data.ImportISN == 0) return;
            String ImportISN = data.ImportISN.ToString();

            String ImportDUE = "null";
            if (data.ImportDUE != string.Empty)
            {
                ImportDUE = "'" + data.ImportDUE + "'";
            }
            String ExportTime = "null";
            if (data.ExportTime != DateTime.MinValue)
            {
                ExportTime = "'" + data.ExportTime.ToString("yyyy-MM-dd hh:mm:ss") + "'";
            }
            String ExportISN = "null";
            if (data.ExportISN != 0)
            {
                ExportISN = data.ExportISN.ToString();
            }
            String ExportDUE = "null";
            if (data.ExportDUE != string.Empty)
            {
                ExportDUE = "'" + data.ExportDUE + "'";
            }
            cmd.CommandText = "INSERT INTO PROTOCOL([TNAME],[IMPORTTIME],[IMPORTISN],[IMPORTDUE],[EXPORTTIME],[EXPORTISN],[EXPORTDUE]) VALUES(" + tname + "," + ImportTime + "," + ImportISN + "," + ImportDUE + "," + ExportTime + "," + ExportISN + "," + ExportDUE + ")";
            cmd.ExecuteNonQuery();
        }

        /// <summary>
        /// выводит все isn - ы выгруженых документов
        ///</summary>
        public OleDbDataReader GetISNs()
        {
            String sql = "SELECT EXPORTISN FROM PROTOCOL WHERE TNAME='RC' ORDER BY EXPORTISN";
            OleDbCommand cmd = new OleDbCommand(sql, Connection);
            OleDbDataReader dr = cmd.ExecuteReader();
            return dr;
        }

        /// <summary>
        /// выводит количество выгруженых документов
        ///</summary>
        public Int32 GetISNsCount()
        {
            String sql = "SELECT Count(EXPORTISN) FROM PROTOCOL WHERE TNAME='RC'";
            OleDbCommand cmd = new OleDbCommand(sql, Connection);
            return (Int32)cmd.ExecuteScalar();
        }

        /// <summary>
        /// выводит количество выгруженых документов
        ///</summary>
        public Boolean isIsnRc(Int32 isn)
        {
            String sql = "SELECT Count(EXPORTISN) FROM PROTOCOL WHERE (TNAME='RC') and (EXPORTISN=" + isn.ToString() + ")";
            OleDbCommand cmd = new OleDbCommand(sql, Connection);
            Int32 result = (Int32)cmd.ExecuteScalar();
            return (result > 0) ? true : false;
        }

        /// <summary>
        /// выводит ISN импортированого документа
        ///</summary>
        public Int32 getImportIsnRc(Int32 isn)
        {
            String sql = "SELECT IMPORTISN FROM PROTOCOL WHERE (TNAME='RC') and (EXPORTISN=" + isn.ToString() + ")";
            OleDbCommand cmd = new OleDbCommand(sql, Connection);
            Int32 result = (Int32)cmd.ExecuteScalar();
            return result;
        }

        /// <summary>
        /// позволяет сопоставить isn выгрузки и загрузки
        ///</summary>
        public void setNewIsn(String Table, Int32 OldIsn, Int32 NewIsn)
        {
            String sql;
            OleDbCommand cmd;
            try
            {
                sql = "UPDATE PROTOCOL SET IMPORTISN=" + NewIsn.ToString() + ", IMPORTTIME=" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + " WHERE (TNAME='" + Table.ToUpper() + "') and (EXPORTISN=" + OldIsn.ToString() + ")";
                cmd = new OleDbCommand(sql, Connection);
                Connection.Open();
                cmd.ExecuteNonQuery();
                Connection.Close();
            }
            catch
            {
                Save(new Data(Table, DateTime.Now, OldIsn, string.Empty, DateTime.Now, NewIsn, string.Empty));
            }
        }

        public string syncCard(string ExportCard)
        {
            return topCard;
        }

        /// <summary>
        /// Кабинет. Заглушка
        ///</summary>
        public Int32 syncCabinet(Int32 ExportCabinet)
        {
            return 0;
        }

        /// <summary>
        /// Считывает значение группы документа из протокола
        /// </summary>
        /// <param name="due">DUE группы документов в первоначальной базе</param>
        /// <returns></returns>
        public string getDueDocgroup(string due)
        {
            string sql = "select IMPORTDUE from DOCGROUP where DUE = '" + due + "'";
            OleDbCommand cmd = new OleDbCommand(sql, Connection);
            object t_due = cmd.ExecuteScalar();
            string result = "";
            if ((t_due != null) && (t_due != DBNull.Value) && ((string)t_due != ""))
            {
                result = (string)t_due;
            }
            return result;
        }

        /// <summary>
        /// метод для синхронизации групп документов 
        ///</summary>
        public string syncDocgroup(XmlNode ExportDocgroup)
        {
            string result = "0.";
            string due = ExportDocgroup.SelectSingleNode("@DUE").Value;
            string rctype = ExportDocgroup.SelectSingleNode("RC_TYPE").InnerText;
            string sql;
            OleDbCommand cmd;
            OleDbDataReader dr;
            object t_due;
            switch (analyz)
            {
                case TAnalys.onetoone:
                    result = due;
                    break;
                case TAnalys.byname:
                    if (checksynctable)
                    {
                        // ищем, возможно поиск такой группы документов уже проводился. сделано для ускорения работы
                        sql = "select IMPORTDUE from DOCGROUP where DUE = '" + due + "'";
                        cmd = new OleDbCommand(sql, Connection);
                        t_due = cmd.ExecuteScalar();
                    }
                    else t_due = null;
                    if ((t_due != null) && (t_due != DBNull.Value) && ((string)t_due != ""))
                    {
                        result = (string)t_due;
                    }
                    else // пытаемся найти по имени сомаго элемента и его родителя нужную группу документов
                    {
                        switch (rctype)
                        {
                            case "1":
                                if (defaultRcIn == "") throw new Exception("LocalDBCompact.defaultRcIn - не задано!");
                                result = defaultRcIn;
                                break;
                            case "2":
                                if (defaultRcLet == "") throw new Exception("LocalDBCompact.defaultRcLet - не задано!");
                                result = defaultRcLet;
                                break;
                            case "3":
                                if (defaultRcOut == "") throw new Exception("LocalDBCompact.defaultRcOut - не задано!");
                                result = defaultRcOut;
                                break;
                        }
                        if (SyncConnection == null) throw new Exception("Этот вид синхронизации предполагает указание соединения для синхронизации!");
                        String cname = ExportDocgroup.SelectSingleNode("CLASSIF_NAME").InnerText;
                        String pname = ExportDocgroup.SelectSingleNode("PARENT/CLASSIF_NAME").InnerText;
                        sql = "select due from " + sqlprefix + "docgroup_cl  "
                        + "where (UPPER(CLASSIF_NAME) like '%" + cname.ToUpper() + "%') "
                        + " and (DUE like '" + topRcDocgroup + "%')";
                        cmd = new OleDbCommand(sql, SyncConnection);
                        dr = cmd.ExecuteReader();
                        string defaultresult = result;
                        if (dr.Read()) result = dr.GetString(0);
                        //else throw new Exception("Группа документов не найдена. используем значение по умолчанию!");
                        if (dr.Read()) result = defaultresult;
                        dr.Close();
                        // сохраняем нужное значение что бы потом не проводить поиск.
                        sql = "update docgroup set IMPORTDUE='" + result + "' WHERE due='" + due + "'";
                        cmd = new OleDbCommand(sql, Connection);
                        cmd.ExecuteNonQuery();
                    }
                    break;
                case TAnalys.bytable:
                    // ищем заданые значения
                    sql = "select IMPORTDUE from DOCGROUP where DUE = '" + due + "'";
                    cmd = new OleDbCommand(sql, Connection);
                    t_due = cmd.ExecuteScalar();
                    if ((t_due != null) && (t_due != DBNull.Value) && ((string)t_due != ""))
                    {
                        result = (string)t_due;
                    }
                    else // если не все значения были заданы, пытаемся использовать значения по умолчанию
                    {
                        switch (rctype)
                        {
                            case "1":
                                if (defaultRcIn == "") throw new Exception("LocalDBCompact.defaultRcIn - не задано!");
                                result = defaultRcIn;
                                break;
                            case "2":
                                if (defaultRcLet == "") throw new Exception("LocalDBCompact.defaultRcLet - не задано!");
                                result = defaultRcLet;
                                break;
                            case "3":
                                if (defaultRcOut == "") throw new Exception("LocalDBCompact.defaultRcOut - не задано!");
                                result = defaultRcOut;
                                break;
                        }
                    }
                    break;
                default: // на всякий случай
                    switch (rctype)
                    {
                        case "1":
                            if (defaultRcIn == "") throw new Exception("LocalDBCompact.defaultRcIn - не задано!");
                            result = defaultRcIn;
                            break;
                        case "2":
                            if (defaultRcLet == "") throw new Exception("LocalDBCompact.defaultRcLet - не задано!");
                            result = defaultRcLet;
                            break;
                        case "3":
                            if (defaultRcOut == "") throw new Exception("LocalDBCompact.defaultRcOut - не задано!");
                            result = defaultRcOut;
                            break;
                    }
                    break;
            }
            return result;
        }

        /// <summary>
        /// метод для синхронизации рубрик
        ///</summary>
        public string syncRubric(XmlNode ExportRubric)
        {
            string result = "0.";
            Int32 _isn = 0;
            Int32 isn = Int32.Parse(ExportRubric.SelectSingleNode("ISN_NODE").InnerText);
            Int32 isnode = Int32.Parse(ExportRubric.SelectSingleNode("IS_NODE").InnerText);
            string code = ExportRubric.SelectSingleNode("RUBRIC_CODE").InnerText;
            string name = ExportRubric.SelectSingleNode("CLASSIF_NAME").InnerText;
            string note = (ExportRubric.SelectNodes("NOTE").Count > 0) ? ExportRubric.SelectSingleNode("NOTE").InnerText : "";
            string sql;
            OleDbCommand cmd;
            OleDbDataReader dr;
            object t_due;
            switch (analyz)
            {
                case TAnalys.onetoone:
                    sql = "select DUE from RUBRIC_CL where ISN_NODE = " + isn.ToString();
                    cmd = new OleDbCommand(sql, SyncConnection);
                    t_due = cmd.ExecuteScalar();
                    result = (string)t_due;
                    break;
                case TAnalys.byname:
                    // ищем, возможно поиск такая рубрика уже проводился. сделано для ускорения работы
                    sql = "select IMPORTDUE from RUBRIC_CL where ISN_NODE = '" + isn.ToString() + "'";
                    cmd = new OleDbCommand(sql, Connection);
                    t_due = cmd.ExecuteScalar();
                    if ((t_due != null) && (t_due != DBNull.Value) && ((string)t_due != ""))
                    {
                        result = (string)t_due;
                    }
                    else // пытаемся найти по имени сомаго элемента и его родителя нужную группу документов
                    {
                        if (SyncConnection == null) throw new Exception("Этот вид синхронизации предполагает указание соединения для синхронизации!");
                        sql = "select DUE,ISN_NODE from " + sqlprefix + "RUBRIC_CL "
                        + "where (UPPER(CLASSIF_NAME) = '" + name.ToUpper() + "') and (DUE like '" + topRubric + "%') and (ISN_NODE>0)";
                        cmd = new OleDbCommand(sql, SyncConnection);
                        dr = cmd.ExecuteReader();
                        if (dr.Read())
                        {
                            result = dr.GetString(0);
                            object res = dr.GetValue(1);
                            _isn = Int32.Parse(res.ToString());
                            //if (dr.Read()) throw new Exception("Существует больше одного значения рубрики!\n" + result);
                        }
                        else
                        {
                            sql = "select DUE,ISN_NODE from " + sqlprefix + "RUBRIC_CL "
                            + "where (UPPER(CLASSIF_NAME) like '%" + name.ToUpper() + "') "
                            + " and (DUE like '" + topRubric + "%') and (ISN_NODE>0)";
                            cmd = new OleDbCommand(sql, SyncConnection);
                            dr = cmd.ExecuteReader();
                            Boolean flag = false;
                            if (dr.Read())
                            {
                                result = dr.GetString(0);
                                object res1 = dr.GetValue(1);
                                _isn = Int32.Parse(res1.ToString());
                                flag = true;
                                //if (dr.Read()) throw new Exception("Существует больше одного значения рубрики!\n" + result);
                            }
                            else
                            {
                                sql = "select DUE,ISN_NODE from " + sqlprefix + "RUBRIC_CL "
                                + "where (UPPER(CLASSIF_NAME) like '%" + name.ToUpper() + "') "
                                + " and (ISN_NODE>0)";
                                cmd = new OleDbCommand(sql, SyncConnection);
                                dr = cmd.ExecuteReader();
                                if (dr.Read())
                                {
                                    result = dr.GetString(0);
                                    object res2 = dr.GetValue(1);
                                    _isn = Int32.Parse(res2.ToString());
                                    flag = true;
                                }

                            }
                            if (!flag)
                            {
                                //throw new Exception("Группа документов не найдена. используем значение по умолчанию!");
                                //if (dr.Read()) throw new Exception("Существует больше одного значения!");
                                OleDbCommand ExecCmd = new OleDbCommand();
                                ExecCmd.Connection = SyncConnection;
                                ExecCmd.CommandType = CommandType.StoredProcedure;
                                ExecCmd.CommandText = "GET_SEQ";
                                ExecCmd.Parameters.Add("asname", OleDbType.VarChar);
                                ExecCmd.Parameters["asname"].Value = "SEQ_CLASSIF";
                                ExecCmd.Parameters.Add("airet", OleDbType.Integer);
                                ExecCmd.Parameters["airet"].Direction = ParameterDirection.Output;
                                ExecCmd.ExecuteNonQuery();
                                Int32 isnnode = Int32.Parse(ExecCmd.Parameters["airet"].Value.ToString());

                                if (SyncConnection.Provider.ToLower().IndexOf("ora") > -1)
                                {
                                    ExecCmd.CommandText = "SELECT DISTINCT Base36(" + isnnode.ToString() + ") FROM WU";
                                }
                                else
                                {
                                    ExecCmd.CommandText = "SELECT dbo.Base36(" + isnnode.ToString() + ")";
                                }
                                ExecCmd.Parameters.Clear();
                                ExecCmd.CommandType = CommandType.Text;
                                dr = ExecCmd.ExecuteReader();
                                dr.Read();
                                String DUE = dr[0].ToString();
                                DUE = topRubric + DUE;
                                dr.Close();
                                sql = "select MAX(WEIGHT) from RUBRIC_CL WHERE ISN_HIGH_NODE=" + topRubricISN.ToString();
                                cmd = new OleDbCommand(sql, SyncConnection);
                                object t_weight = cmd.ExecuteScalar();
                                Int32 weight = 1;
                                if ((t_weight != null) && (t_weight != DBNull.Value)) weight = Int32.Parse(t_weight.ToString()) + 1;
                                sql = "INSERT INTO RUBRIC_CL (DUE,ISN_NODE,ISN_HIGH_NODE,LAYER,IS_NODE,WEIGHT,MAXDUE," +
                                "CLASSIF_NAME,PROTECTED,DELETED,RUBRIC_CODE,NOTE) VALUES ('" + DUE + "'," + isnnode.ToString() +
                                "," + topRubricISN.ToString() + ",(select LAYER+1 from RUBRIC_CL WHERE ISN_NODE=" +
                                topRubricISN.ToString() + ")," + isnode.ToString() + "," + weight.ToString() +
                                ",'" + DUE + "','" + name.Replace('\"', '`').Replace('\'', '`') +
                                "',0,0,'" + code + "','" + note.Replace('\"', '`').Replace('\'', '`') + "')";
                                cmd = new OleDbCommand(sql, SyncConnection);
                                cmd.ExecuteNonQuery();
                                result = DUE;
                                _isn = isnnode;
                            }
                        }
                        dr.Close();
                        // сохраняем нужное значение что бы потом не проводить поиск.
                        sql = "update RUBRIC_CL set IMPORTDUE='" + result + "', IMPORTISN=" + _isn.ToString() + " WHERE ISN_NODE = " + isn.ToString(); ;
                        cmd = new OleDbCommand(sql, Connection);
                        cmd.ExecuteNonQuery();
                    }
                    break;
                case TAnalys.bytable:
                    // ищем заданые значения
                    sql = "select IMPORTDUE from RUBRIC_CL where ISN_NODE = '" + isn.ToString() + "'";
                    cmd = new OleDbCommand(sql, Connection);
                    t_due = cmd.ExecuteScalar();
                    if ((t_due != null) && (t_due != DBNull.Value) && ((string)t_due != ""))
                    {
                        result = (string)t_due;
                    }
                    break;
                default: // на всякий случай
                    return "";
            }
            return result;
        }
        /// <summary>
        /// приведение даты в формат необходимый для загрузки
        ///</summary>       
        public string convertDate(string ExportDate)
        {
            if (ExportDate == "") return "";
            return DateTime.Parse(ExportDate).ToString("yyyyMMdd");
        }

        /// <summary>
        /// метод для синхронизации грифов доступа
        ///</summary>
        public Int32 syncSecurlevel(XmlNode ExportSecurlevel)
        {
            string sql;
            OleDbCommand cmd;
            OleDbDataReader dr;
            object t_isn;
            Int32 result2 = Int32.Parse(ExportSecurlevel.SelectSingleNode("@ISN").InnerText);
            Int32 result = result2;
            switch (analyz)
            {
                case TAnalys.onetoone:
                    return result;
                case TAnalys.byname:
                    // ищем, возможно поиск такого грифа доступа уже проводился. сделано для ускорения работы
                    sql = "select IMPORTISN from SECURITY where SECURLEVEL = " + result;
                    cmd = new OleDbCommand(sql, Connection);
                    t_isn = cmd.ExecuteScalar();
                    if ((t_isn != null) && (t_isn != DBNull.Value) && ((Int32)t_isn != 0))
                    {
                        result = (Int32)t_isn;
                    }
                    else
                    {
                        if (SyncConnection == null) throw new Exception("Этот вид синхронизации предполагает указание соединения для синхронизации!");
                        if (ExportSecurlevel == null) throw new Exception("Не задано название грифа доступа. Поиск не возможен!");
                        string name = ExportSecurlevel.InnerText;
                        sql = "select SECURLEVEL from " + sqlprefix + "SECURITY_CL WHERE (UPPER(GRIF_NAME) like '%" + name.ToUpper() + "%') ";
                        cmd = new OleDbCommand(sql, SyncConnection);
                        dr = cmd.ExecuteReader();
                        if (dr.Read())
                        {
                            object res = dr.GetValue(0);
                            result = Int32.Parse(res.ToString());
                            if (dr.Read()) throw new Exception("Существует больше одного значения грифа доступа!\n" + result);
                            dr.Close();
                        }
                        else
                        {
                            // проверить украинские названия и псевдонимы
                            switch (name.ToUpper())
                            {
                                case "ЗАГАЛЬНЫЙ":
                                case "ЗАГАЛЬНЕ":
                                    sql = "select SECURLEVEL from " + sqlprefix + "SECURITY_CL WHERE (UPPER(GRIF_NAME) like '%ОБЩИЙ%')";
                                    break;
                                case "ДСК":
                                    sql = "select SECURLEVEL from " + sqlprefix + "SECURITY_CL WHERE (UPPER(GRIF_NAME) like '%ДСП%') ";
                                    break;
                                default:
                                    sql = "";
                                    break;
                            }
                            if (sql != "")
                            {
                                cmd = new OleDbCommand(sql, SyncConnection);
                                dr = cmd.ExecuteReader();
                                if (dr.Read())
                                {
                                    object res = dr.GetValue(0);
                                    result = Int32.Parse(res.ToString());
                                    if (dr.Read()) throw new Exception("Существует больше одного значения грифа доступа!\n" + result.ToString());
                                }
                            }
                            else // Если не нашли даже на украинском добавим значения
                            {
                                sql = "INSERT INTO " + sqlprefix + "SECURITY_CL (SECURLEVEL,GRIF_NAME,DELETED,PROTECTED,WEIGHT) " +
                                    "VALUES ((SELECT MAX(SECURLEVEL)+1 FROM " + sqlprefix + "SECURITY_CL) , '" + name + "'," +
                                    "0, 0,(SELECT MAX(WEIGHT)+1 FROM " + sqlprefix + "SECURITY_CL))";
                                cmd = new OleDbCommand(sql, SyncConnection);
                                cmd.ExecuteNonQuery();
                                // ИЩЕМ SECURLEVEL
                                sql = "SELECT MAX(SECURLEVEL) FROM " + sqlprefix + "SECURITY_CL ";
                                cmd = new OleDbCommand(sql, SyncConnection);
                                object res1 = cmd.ExecuteScalar();
                                result = Int32.Parse(res1.ToString()); // Сомнительно что он не будет найден
                            }
                        }
                        // сохраняем нужное значение что бы потом не проводить поиск.
                        sql = "update SECURITY set IMPORTISN=" + result + " WHERE SECURLEVEL=" + result2.ToString();
                        cmd = new OleDbCommand(sql, Connection);
                        cmd.ExecuteNonQuery();

                    }
                    break;
                case TAnalys.bytable:
                    // ищем заданый гриф доступа
                    sql = "select IMPORTISN from SECURITY where SECURLEVEL = " + result;
                    cmd = new OleDbCommand(sql, Connection);
                    t_isn = cmd.ExecuteScalar();
                    if ((t_isn != null) && (t_isn != DBNull.Value) && ((Int32)t_isn != 0))
                    {
                        result = (Int32)t_isn;
                    }
                    else return 1;
                    break;
                default:
                    return 1;
            }
            return result;
        }

        /// <summary>
        /// Обработка и синхронизация контроля
        ///</summary>
        public Int32 syncControlState(string ExportControlState)
        {
            if (ExportControlState == "") return 0;
            return Int32.Parse(ExportControlState);
        }

        /// <summary>
        /// Вычисление поля кому
        ///</summary>
        public string syncWho(XmlNode ExportWhos)
        {
            XmlNodeList persons = ExportWhos.SelectNodes("DOC_WHOS/DUE_PERSON");
            string result = "";
            for (int i = 0; i < persons.Count; i++)
            {
                if (i == 0)
                {
                    result = syncDepartment(persons[i]);
                }
                else
                {
                    result = result+"|" + syncDepartment(persons[i]);
                }
            }
            return result;
        }

        /// <summary>
        /// метод для синхронизации видов доставки
        ///</summary>
        public Int32 syncDelivery(XmlNode ExportDelivery)
        {
            string sql;
            OleDbCommand cmd;
            OleDbDataReader dr;
            object t_isn;
            Int32 result = Int32.Parse(ExportDelivery.SelectSingleNode("@ISN").InnerText);
            switch (analyz)
            {
                case TAnalys.onetoone:
                    return result;
                case TAnalys.byname:
                    sql = "select IMPORTISN from DELIVERY_CL where ISN_LCLASSIF = " + result;
                    cmd = new OleDbCommand(sql, Connection);
                    t_isn = cmd.ExecuteScalar();
                    if ((t_isn != null) && (t_isn != DBNull.Value) && ((Int32)t_isn != 0))
                    {
                        result = (Int32)t_isn;
                    }
                    else
                    {
                        if (SyncConnection == null) throw new Exception("Этот вид синхронизации предполагает указание соединения для синхронизации!");
                        if (ExportDelivery == null) throw new Exception("Не задано название ВИДА ДОСТАВКИ. Поиск не возможен!");
                        string name = ExportDelivery.InnerText;
                        sql = "select ISN_LCLASSIF from " + sqlprefix + "DELIVERY_CL WHERE (UPPER(CLASSIF_NAME) = '" + name.ToUpper() + "') ";
                        cmd = new OleDbCommand(sql, SyncConnection);
                        dr = cmd.ExecuteReader();
                        if (dr.Read())
                        {
                            object res = dr.GetValue(0);
                            result = Int32.Parse(res.ToString());
                            if (dr.Read()) throw new Exception("Существует больше одного значения вида доставки!\n" + result.ToString());
                            dr.Close();
                            // сохраняем нужное значение что бы потом не проводить поиск.
                            sql = "update DELIVERY_CL set IMPORTISN=" + result + " WHERE ISN_LCLASSIF=" + ExportDelivery.SelectSingleNode("@ISN").InnerText;
                            cmd = new OleDbCommand(sql, Connection);
                            cmd.ExecuteNonQuery();
                        }
                        else
                        {
                            dr.Close();
                            Int32 oldisn = result;
                            // проверить украинские названия и псевдонимы
                            switch (name.ToUpper())
                            {
                                case "ПОШТА":
                                    sql = "select ISN_LCLASSIF from " + sqlprefix + "DELIVERY_CL WHERE (UPPER(CLASSIF_NAME) like '%ПОЧТА%') ";
                                    break;
                                case "ПОЧТА":
                                    sql = "select ISN_LCLASSIF from " + sqlprefix + "DELIVERY_CL WHERE (UPPER(CLASSIF_NAME) like '%ПОШТА%') ";
                                    break;
                                case "ОСОБИСТО":
                                    sql = "select ISN_LCLASSIF from " + sqlprefix + "DELIVERY_CL WHERE (UPPER(CLASSIF_NAME) like '%ЛИЧНО%') ";
                                    break;
                                case "ЛИЧНО":
                                    sql = "select ISN_LCLASSIF from " + sqlprefix + "DELIVERY_CL WHERE (UPPER(CLASSIF_NAME) like '%ОСОБИСТО%') ";
                                    break;
                                default:
                                    sql = "";
                                    break;
                            }
                            if (sql != "")
                            {
                                cmd = new OleDbCommand(sql, SyncConnection);
                                dr = cmd.ExecuteReader();
                                if (dr.Read())
                                {
                                    object res3 = dr.GetValue(0);
                                    result = Int32.Parse(res3.ToString());
                                    if (dr.Read()) throw new Exception("Существует больше одного значения вида доставки!\n" + result.ToString());
                                }
                                else
                                {
                                    sql = "INSERT INTO " + sqlprefix + "DELIVERY_CL (ISN_LCLASSIF,CLASSIF_NAME,PROTECTED,DELETED,WEIGHT) " +
                                        "VALUES ((SELECT MAX(ISN_LCLASSIF)+1 FROM " + sqlprefix + "DELIVERY_CL) , '" + name + "'," +
                                        "0, 0,(SELECT MAX(WEIGHT)+1 FROM " + sqlprefix + "DELIVERY_CL))";
                                    cmd = new OleDbCommand(sql, SyncConnection);
                                    cmd.ExecuteNonQuery();
                                    // ИЩЕМ SECURLEVEL
                                    sql = "SELECT MAX(ISN_LCLASSIF) FROM " + sqlprefix + "DELIVERY_CL ";
                                    cmd = new OleDbCommand(sql, SyncConnection);
                                    object res2 = dr.GetValue(0);
                                    result = Int32.Parse(res2.ToString());
                                }
                                dr.Close();

                            }
                            else // Если не нашли даже на украинском добавим значения
                            {
                                sql = "INSERT INTO " + sqlprefix + "DELIVERY_CL (ISN_LCLASSIF,CLASSIF_NAME,PROTECTED,DELETED,WEIGHT) " +
                                    "VALUES ((SELECT MAX(ISN_LCLASSIF)+1 FROM " + sqlprefix + "DELIVERY_CL) , '" + name + "'," +
                                    "0, 0,(SELECT MAX(WEIGHT)+1 FROM " + sqlprefix + "DELIVERY_CL))";
                                cmd = new OleDbCommand(sql, SyncConnection);
                                cmd.ExecuteNonQuery();
                                // ИЩЕМ SECURLEVEL
                                sql = "SELECT MAX(ISN_LCLASSIF) FROM " + sqlprefix + "DELIVERY_CL ";
                                cmd = new OleDbCommand(sql, SyncConnection);
                                object res1 = cmd.ExecuteScalar(); // Сомнительно что он не будет найден
                                result = Int32.Parse(res1.ToString());
                            }
                            // сохраняем нужное значение что бы потом не проводить поиск.
                            sql = "update DELIVERY_CL set IMPORTISN=" + result + " WHERE ISN_LCLASSIF=" + oldisn.ToString();
                            cmd = new OleDbCommand(sql, Connection);
                            cmd.ExecuteNonQuery();
                        }

                    }
                    break;
                case TAnalys.bytable:
                    // ищем заданый гриф доступа
                    sql = "select IMPORTISN from DELIVERY_CL where ISN_LCLASSIF = " + result;
                    cmd = new OleDbCommand(sql, Connection);
                    t_isn = cmd.ExecuteScalar();
                    if ((t_isn != null) && (t_isn != DBNull.Value) && ((Int32)t_isn != 0))
                    {
                        result = (Int32)t_isn;
                    }
                    else return 1;
                    break;
                default:
                    return 1;
            }
            return result;
        }

        /// <summary>
        /// метод для синхронизации организаций
        ///</summary>
        public string syncOrganiz(XmlNode ExportOrganiz)
        {
            string sql;
            OleDbCommand cmd;
            OleDbDataReader dr;
            string importDUE;
            string xmlDUE;
            Int32 importISN;
            Int32 xmlISN;
            object t_due;
            xmlDUE = ExportOrganiz.SelectSingleNode("@DUE").InnerText;
            xmlISN = Int32.Parse(ExportOrganiz.SelectSingleNode("@ISN").InnerText);
            switch (analyz)
            {
                case TAnalys.onetoone:
                    return xmlDUE;
                case TAnalys.byname:
                    // ищем, возможно такую организацию уже регистрировали
                    if (checksynctable)
                    {
                        sql = "select IMPORTDUE from ORGANIZ_CL where DUE = '" + xmlDUE + "'";
                        cmd = new OleDbCommand(sql, Connection);
                        t_due = cmd.ExecuteScalar();
                        cmd.Dispose();
                    }
                    else t_due = null;

                    if ((t_due != null) && (t_due != DBNull.Value))
                    {
                        return (string)t_due;
                    }
                    else
                    {
                        if (SyncConnection == null) throw new Exception("Этот вид синхронизации предполагает указание соединения для синхронизации!");
                        if (ExportOrganiz.SelectNodes("CLASSIF_NAME").Count == 0) throw new Exception("Не задано название организации. Поиск не возможен!");
                        string name = ExportOrganiz.SelectSingleNode("CLASSIF_NAME").InnerText;

                        sql = "select DUE,ISN_NODE,IS_NODE from " + sqlprefix + "ORGANIZ_CL WHERE (UPPER(CLASSIF_NAME) = '" +
                            name.ToUpper() + "')";
                        cmd = new OleDbCommand(sql, SyncConnection);
                        dr = cmd.ExecuteReader();
                        if (dr.Read() && (!dr.IsDBNull(0)))
                        {
                            if (dr.GetValue(2).ToString() == "1")
                            {
                                importDUE = dr.GetString(0);
                                importISN = Int32.Parse(dr.GetValue(1).ToString());
                                // сохраняем нужное значение что бы потом не проводить поиск.
                                sql = "update ORGANIZ_CL set IMPORTDUE='" + importDUE + "',IMPORTISN=" + importISN.ToString() +
                                      " WHERE DUE='" + xmlDUE + "'";
                                cmd = new OleDbCommand(sql, Connection);
                                cmd.ExecuteNonQuery();
                                cmd.Dispose();
                                dr.Close();
                                return importDUE;
                            }
                            else
                            {
                                sql = "update " + sqlprefix + "ORGANIZ_CL set CLASSIF_NAME = '" + name + " (папка)' where ISN_NODE = '" + Int32.Parse(dr.GetValue(1).ToString()) + "'";
                                cmd = new OleDbCommand(sql, SyncConnection);
                                cmd.ExecuteNonQuery();

                                insertOrganiz(name, topOrganizISN,
                                ((ExportOrganiz.SelectNodes("ZIPCODE").Count > 0) ? ExportOrganiz.SelectSingleNode("ZIPCODE").InnerText : ""),
                                 ((ExportOrganiz.SelectNodes("CITY").Count > 0) ? ExportOrganiz.SelectSingleNode("CITY").InnerText : ""),
                                 ((ExportOrganiz.SelectNodes("ADDRESS").Count > 0) ? ExportOrganiz.SelectSingleNode("ADDRESS").InnerText : ""),
                                 ((ExportOrganiz.SelectNodes("NOTE").Count > 0) ? ExportOrganiz.SelectSingleNode("NOTE").InnerText : ""),
                                 out importISN, out importDUE);
                                // сохраняем нужное значение что бы потом не проводить поиск.
                                sql = "update ORGANIZ_CL set IMPORTDUE='" + importDUE + "',IMPORTISN=" + importISN.ToString() +
                                    " WHERE DUE='" + xmlDUE + "'";
                                cmd = new OleDbCommand(sql, Connection);
                                cmd.ExecuteNonQuery();
                                cmd.Dispose();
                                return importDUE;

                            }
                        }                     
                        else // если не нашли по имени то должны добавить организацию как новую 
                        {
                            insertOrganiz(name, topOrganizISN,
                                ((ExportOrganiz.SelectNodes("ZIPCODE").Count > 0) ? ExportOrganiz.SelectSingleNode("ZIPCODE").InnerText : ""),
                                 ((ExportOrganiz.SelectNodes("CITY").Count > 0) ? ExportOrganiz.SelectSingleNode("CITY").InnerText : ""),
                                 ((ExportOrganiz.SelectNodes("ADDRESS").Count > 0) ? ExportOrganiz.SelectSingleNode("ADDRESS").InnerText : ""),
                                 ((ExportOrganiz.SelectNodes("NOTE").Count > 0) ? ExportOrganiz.SelectSingleNode("NOTE").InnerText : ""),
                                 out importISN, out importDUE);
                            // сохраняем нужное значение что бы потом не проводить поиск.
                            sql = "update ORGANIZ_CL set IMPORTDUE='" + importDUE + "',IMPORTISN=" + importISN.ToString() +
                                " WHERE DUE='" + xmlDUE + "'";
                            cmd = new OleDbCommand(sql, Connection);
                            cmd.ExecuteNonQuery();
                            cmd.Dispose();
                            return importDUE;
                        }
                    }
                case TAnalys.bytable:
                    // ищем заданый гриф доступа
                    sql = "select IMPORTDUE from ORGANIZ_CL where DUE = " + xmlDUE;
                    cmd = new OleDbCommand(sql, Connection);
                    t_due = cmd.ExecuteScalar();
                    cmd.Dispose();
                    if ((t_due != null) && (t_due != DBNull.Value))
                    {
                        importDUE = (string)t_due;
                    }
                    else
                    {
                        importDUE = "";
                    }
                    return importDUE;
                default:
                    return "";
            }
        }
        /// <summary>
        /// метод для синхронизации ISN_CONTACT
        ///</summary>
        public string syncCoverContact(String ISN_ORGANIZ, Int32 isn_doc)
        {
            string sql;
            string isn_contact;
            OleDbCommand cmd;
            OleDbDataReader dr;
                        if (SyncConnection == null) throw new Exception("Этот вид синхронизации предполагает указание соединения для синхронизации!");
                        if (ISN_ORGANIZ == "") throw new Exception("Не задан DUE организации. Поиск не возможен!");

                        sql = "select ISN_CONTACT from " + sqlprefix + "CONTACT WHERE ISN_ORGANIZ = '" +
                            ISN_ORGANIZ + "'";
                        cmd = new OleDbCommand(sql, SyncConnection);
                        dr = cmd.ExecuteReader();
                        if (dr.Read() && (!dr.IsDBNull(0)))
                        {
                            isn_contact = dr.GetValue(0).ToString();
                            return isn_contact;
                        }
                        else
                        {
                            throw new Exception("Не найдена организация с указанным DUE!");
                        }
        }

        private void insertOrganiz(String name, Int32 topOrganizISN, String zipcode, String city, String address, String note, out Int32 isn, out String due)
        {
            OleDbCommand ExecCmd = new OleDbCommand();
            ExecCmd.Connection = SyncConnection;
            ExecCmd.CommandType = CommandType.StoredProcedure;
            ExecCmd.CommandText = "GET_SEQ";
            ExecCmd.Parameters.Add("asname", OleDbType.VarChar);
            ExecCmd.Parameters["asname"].Value = "SEQ_CLASSIF";
            ExecCmd.Parameters.Add("airet", OleDbType.Integer);
            ExecCmd.Parameters["airet"].Direction = ParameterDirection.Output;
            ExecCmd.ExecuteNonQuery();
            isn = Int32.Parse(ExecCmd.Parameters["airet"].Value.ToString());

            if (SyncConnection.Provider.ToLower().IndexOf("ora") > -1)
            {
                ExecCmd.CommandText = "SELECT DISTINCT Base36(" + isn.ToString() + ") FROM WU";
            }
            else
            {
                ExecCmd.CommandText = "SELECT dbo.Base36(" + isn.ToString() + ")";
            }
            ExecCmd.Parameters.Clear();
            ExecCmd.CommandType = CommandType.Text;
            OleDbDataReader dr = ExecCmd.ExecuteReader();
            dr.Read();
            due = dr[0].ToString();
            due = topOrganiz + due;
            dr.Close();
            Int32 layer = 0;
            String sql = "select LAYER+1 from " + sqlprefix + "ORGANIZ_CL WHERE DUE='" + topOrganiz + "'";
            OleDbCommand cmd = new OleDbCommand(sql, SyncConnection);
            object res = cmd.ExecuteScalar();
            layer = Int32.Parse(res.ToString());
            sql = "INSERT INTO " + sqlprefix + "ORGANIZ_CL (DUE, ISN_NODE, ISN_HIGH_NODE, LAYER, " +
                "IS_NODE,WEIGHT,CLASSIF_NAME,PROTECTED,DELETED,ZIPCODE,CITY,ADDRESS,MAIL_FOR_ALL,NOTE) " +
                 "VALUES ('" + due + "'," + isn.ToString() + "," + topOrganizISN.ToString() + "," + layer.ToString() + ",1," +
                 "(SELECT MAX(WEIGHT)+1 FROM " + sqlprefix + "ORGANIZ_CL),'" + name + "',0,0,'" +
                 zipcode + "','" + city + "','" + address + "',0,'" + note + "')";
            cmd = new OleDbCommand(sql, SyncConnection);
            cmd.ExecuteNonQuery();
            sql = "INSERT INTO " + sqlprefix + "CONTACT (ISN_CONTACT,ISN_ORGANIZ,ORDERNUM,DELETED,EDS_FLAG,ENCRYPT_FLAG,MAIL_FORMAT) " +
                "VALUES (" + isn.ToString() + "," + isn.ToString() + ",0,0,0,0,2)";
            cmd = new OleDbCommand(sql, SyncConnection);
            cmd.ExecuteNonQuery();
        }

        /// <summary>
        /// метод для синхронизации организаций
        ///</summary>
        public Int32 syncOrganizISN(XmlNode ExportOrganiz)
        {
            string sql;
            OleDbCommand cmd;
            OleDbDataReader dr;
            string importDUE;
            string xmlDUE;
            Int32 importISN;
            Int32 xmlISN;
            object t_isn;

            xmlDUE = ExportOrganiz.SelectSingleNode("@DUE").InnerText;
            xmlISN = Int32.Parse(ExportOrganiz.SelectSingleNode("@ISN").InnerText);
            switch (analyz)
            {
                case TAnalys.onetoone:
                    return xmlISN;
                case TAnalys.byname:
                    // ищем, возможно такую организацию уже регистрировали
                    if (checksynctable)
                    {
                        sql = "select IMPORTISN from ORGANIZ_CL where ISN_NODE = " + xmlISN;
                        cmd = new OleDbCommand(sql, Connection);
                        t_isn = cmd.ExecuteScalar();
                        cmd.Dispose();
                    }
                    else t_isn = null;

                    if ((t_isn != null) && (t_isn != DBNull.Value))
                    {
                        return (Int32)t_isn;
                    }
                    else
                    {
                        if (SyncConnection == null) throw new Exception("Этот вид синхронизации предполагает указание соединения для синхронизации!");
                        if (ExportOrganiz.SelectNodes("CLASSIF_NAME").Count == 0) throw new Exception("Не задано название организации. Поиск не возможен!");
                        string name = ExportOrganiz.SelectSingleNode("CLASSIF_NAME").InnerText;

                        sql = "select DUE,ISN_NODE from " + sqlprefix + "ORGANIZ_CL WHERE (UPPER(CLASSIF_NAME) = '" +
                            name.ToUpper() + "')";
                        cmd = new OleDbCommand(sql, SyncConnection);
                        dr = cmd.ExecuteReader();
                        if (dr.Read() && (!dr.IsDBNull(0)))
                        {
                            importDUE = dr.GetString(0);
                            importISN = Int32.Parse(dr.GetValue(1).ToString());
                            // сохраняем нужное значение что бы потом не проводить поиск.
                            sql = "update ORGANIZ_CL set IMPORTDUE='" + importDUE + "',IMPORTISN=" + importISN.ToString() +
                                  " WHERE ISN_NODE=" + xmlISN;
                            cmd = new OleDbCommand(sql, Connection);
                            cmd.ExecuteNonQuery();
                            cmd.Dispose();
                            dr.Close();
                            return importISN;
                        }
                        else // если не нашли по имени то должны добавить организацию как новую 
                        {
                            insertOrganiz(name, topOrganizISN,
                                ((ExportOrganiz.SelectNodes("ZIPCODE").Count > 0) ? ExportOrganiz.SelectSingleNode("ZIPCODE").InnerText : ""),
                                 ((ExportOrganiz.SelectNodes("CITY").Count > 0) ? ExportOrganiz.SelectSingleNode("CITY").InnerText : ""),
                                 ((ExportOrganiz.SelectNodes("ADDRESS").Count > 0) ? ExportOrganiz.SelectSingleNode("ADDRESS").InnerText : ""),
                                 ((ExportOrganiz.SelectNodes("NOTE").Count > 0) ? ExportOrganiz.SelectSingleNode("NOTE").InnerText : ""),
                                 out importISN, out importDUE);
                            // сохраняем нужное значение что бы потом не проводить поиск.
                            sql = "update ORGANIZ_CL set IMPORTDUE='" + importDUE + "',IMPORTISN=" + importISN.ToString() +
                                  " WHERE ISN_NODE=" + xmlISN;
                            cmd = new OleDbCommand(sql, Connection);
                            cmd.ExecuteNonQuery();
                            cmd.Dispose();
                            return importISN;
                        }
                    }
                case TAnalys.bytable:
                    // ищем заданый гриф доступа
                    sql = "select IMPORTDUE from ORGANIZ_CL where DUE = " + xmlDUE;
                    cmd = new OleDbCommand(sql, Connection);
                    t_isn = cmd.ExecuteScalar();
                    cmd.Dispose();
                    if ((t_isn != null) && (t_isn != DBNull.Value))
                    {
                        return (Int32)t_isn;
                    }
                    return 0;
                default:
                    return 0;
            }
        }

        /// <summary>
        /// метод для синхронизации граждан
        ///</summary>
        public Int32 syncCitizen(XmlNode ExportCitizen)
        {
            string sql;
            OleDbCommand cmd;
            OleDbDataReader dr;
            object t_isn;
            Int32 result = Int32.Parse(ExportCitizen.SelectSingleNode("ISN_CITIZEN").InnerText);
            switch (analyz)
            {
                case TAnalys.onetoone:
                    return result;
                case TAnalys.byname:
                    if (checksynctable)
                    {
                        // ищем, возможно поиск такого гражданина уже проводился. сделано для ускорения работы
                        sql = "select IMPORTISN from CITIZEN where ISN_CITIZEN = " + result.ToString();
                        cmd = new OleDbCommand(sql, Connection);
                        t_isn = cmd.ExecuteScalar();
                    }
                    else t_isn = null;
                    if ((t_isn != null) && (t_isn != DBNull.Value) && ((Int32)t_isn != 0))
                    {
                        result = (Int32)t_isn;
                    }
                    else
                    {
                        if (SyncConnection == null) throw new Exception("Этот вид синхронизации предполагает указание соединения для синхронизации!");
                        if (ExportCitizen == null) return 0;
                        string name = ExportCitizen.SelectSingleNode("CITIZEN_SURNAME").InnerText.Replace('\"', '`').Replace('\'', '`');
                        string addr = ((ExportCitizen.SelectNodes("CITIZEN_ADDR").Count > 0) ? ("'" + ExportCitizen.SelectSingleNode("CITIZEN_ADDR").InnerText.Replace('\"', '`').Replace('\'', '`') + "'") : "NULL");
                        string city = ((ExportCitizen.SelectNodes("CITIZEN_CITY").Count > 0) ? ("'" + ExportCitizen.SelectSingleNode("CITIZEN_CITY").InnerText.Replace('\"', '`').Replace('\'', '`') + "'") : "NULL");
                        string zipcode = ((ExportCitizen.SelectNodes("ZIPCODE").Count > 0) ? ("'" + ExportCitizen.SelectSingleNode("ZIPCODE").InnerText + "'") : "NULL");
                        sql = "select ISN_CITIZEN from " + sqlprefix + "CITIZEN WHERE ((UPPER(CITIZEN_SURNAME) = '" +
                            name.ToUpper() + "') and (UPPER(CITIZEN_ADDR) = " + addr.ToUpper() + ") and (UPPER(CITIZEN_CITY)=" + city.ToUpper() + ") )";
                        cmd = new OleDbCommand(sql, SyncConnection);
                        dr = cmd.ExecuteReader();
                        if (dr.Read())
                        {
                            object res = dr.GetValue(0);
                            result = Int32.Parse(res.ToString());
                            if (dr.Read()) throw new Exception("Существует больше одного значения организации!\n" + result.ToString());
                        }
                        else
                        {
                            OleDbCommand ExecCmd = new OleDbCommand();
                            ExecCmd.Connection = SyncConnection;
                            ExecCmd.CommandType = CommandType.StoredProcedure;
                            ExecCmd.CommandText = "GET_SEQ";
                            ExecCmd.Parameters.Add("asname", OleDbType.VarChar);
                            ExecCmd.Parameters["asname"].Value = "SEQ_CLASSIF";
                            ExecCmd.Parameters.Add("airet", OleDbType.Integer);
                            ExecCmd.Parameters["airet"].Direction = ParameterDirection.Output;
                            ExecCmd.ExecuteNonQuery();
                            Int32 isn = Int32.Parse(ExecCmd.Parameters["airet"].Value.ToString());
                            if (name[0] == '\'') name = name.Substring(1, name.Length - 2);
                            if (addr[0] == '\'') addr = addr.Substring(1, addr.Length - 2);
                            if (city[0] == '\'') city = city.Substring(1, city.Length - 2);
                            if (zipcode[0] == '\'') zipcode = zipcode.Substring(1, zipcode.Length - 2);
                            sql = "INSERT INTO " + sqlprefix + "CITIZEN (ISN_CITIZEN,CITIZEN_SURNAME,CITIZEN_CITY,ZIPCODE,CITIZEN_ADDR,DELETED,PROTECTED,WEIGHT) " +
                                "VALUES (" + isn.ToString() + " , '" + name.Replace('\"', '`').Replace('\'', '`') + "','" + city + "','" + zipcode + "','" + addr.Replace('\"', '`').Replace('\'', '`') +
                                "',0, 0,(SELECT MAX(WEIGHT)+1 FROM " + sqlprefix + "CITIZEN))";
                            cmd = new OleDbCommand(sql, SyncConnection);
                            cmd.ExecuteNonQuery();

                            // сохраняем нужное значение что бы потом не проводить поиск.
                            sql = "update CITIZEN set IMPORTISN=" + isn + " WHERE ISN_CITIZEN=" + result.ToString();
                            cmd = new OleDbCommand(sql, Connection);
                            cmd.ExecuteNonQuery();
                            result = isn;
                        }

                    }
                    break;
                case TAnalys.bytable:
                    // ищем заданый гриф доступа
                    sql = "select IMPORTISN from CITIZEN where ISN_CITIZEN = " + result.ToString();
                    cmd = new OleDbCommand(sql, Connection);
                    t_isn = cmd.ExecuteScalar();
                    if ((t_isn != null) && (t_isn != DBNull.Value) && ((Int32)t_isn != 0))
                    {
                        result = (Int32)t_isn;
                    }
                    else return 0;
                    break;
                default:
                    return 0;
            }
            return result;
        }

        /// <summary>
        /// при пустом значении преобразует в DBNull
        ///</summary>
        public object convertInt(string ExportInt)
        {
            if (ExportInt == "") return DBNull.Value;
            int result = 0;
                Int32.TryParse(ExportInt, out result);
            return result;
        }

        /// <summary>
        /// синхронизация подписей
        ///</summary>
        public string syncSign(XmlNode ExportSign)
        {
            XmlNodeList persons = ExportSign.SelectNodes("DOC_SIGN/DUE_PERSON");
            string result = "";
            for (int i = 0; i < persons.Count; i++)
            {
                if (i == 0)
                {
                    result = syncDepartment(persons[i]);
                }
                else
                {
                    result = result+"|" + syncDepartment(persons[i]);
                }
            }
            return result;
        }

        /// <summary>
        /// метод для синхронизации подразделений
        ///</summary>
        public string syncDepartment(XmlNode ExportDepartment)
        {
            string sql;
            OleDbCommand cmd;
            OleDbDataReader dr = null;
            string parentname = "";
            string name = "";
            string importDUE;
            string xmlDUE;
            Int32 importISN;
            object t_isn;
            string isndue = "";
            xmlDUE = ExportDepartment.SelectSingleNode("@DUE").InnerText;
            switch (analyz)
            {
                case TAnalys.onetoone:
                    return xmlDUE;
                case TAnalys.byname:
                    if (checksynctable)
                    {
                        sql = "select IMPORTDUE,IMPORTISN from DEPARTMENT where DUE = '" + xmlDUE + "'";
                        cmd = new OleDbCommand(sql, Connection);
                        dr = cmd.ExecuteReader();
                    }
                    if (checksynctable && dr.Read() && (!dr.IsDBNull(0)))
                    {
                        importDUE = dr.GetString(0);
                        dr.Close();
                        //cmd.Dispose();
                        return importDUE;
                    }
                    else
                    {
                        if (SyncConnection == null) throw new Exception("Этот вид синхронизации предполагает указание соединения для синхронизации!");
                        if (ExportDepartment == null) throw new Exception("Не задано название департамента. Поиск не возможен!");
                        name = ExportDepartment.SelectSingleNode("CLASSIF_NAME").InnerText;
                        if (ExportDepartment.SelectNodes("SURNAME").Count > 0) // если это возможно ищем по фамилии т.к. должность может быть писаться по разному
                        {
                            name = ExportDepartment.SelectSingleNode("SURNAME").InnerText;
                        }
                        // ---------------------- Поиск при наличии родителя ----------------------------------------------
                        if (ExportDepartment.SelectNodes("PARENT/CLASSIF_NAME").Count > 0) // если это возможно ищем по фамилии т.к. должность может быть писаться по разному
                        {
                            parentname = ExportDepartment.SelectSingleNode("PARENT/CLASSIF_NAME").InnerText;
                            if (parentname != "")
                            {
                                sql = "select D1.DUE, D1.ISN_NODE from " + sqlprefix + "DEPARTMENT D1, DEPARTMENT D2 WHERE " +
                                      "(UPPER(D1.CLASSIF_NAME) like '" + name.ToUpper() + "%') " +
                                      "and (D1.ISN_HIGH_NODE=D2.ISN_NODE) AND (D1.DUE like '" + topDepartment +
                                      "%') AND (D2.DUE like '" + topDepartment + "%') AND " +
                                      "(UPPER(D2.CLASSIF_NAME) = '" + parentname.ToUpper() + "') ";
                                cmd = new OleDbCommand(sql, SyncConnection);
                                dr = cmd.ExecuteReader();

                                if (dr.Read())
                                {
                                    importDUE = dr.GetString(0);
                                    importISN = Int32.Parse(dr.GetValue(1).ToString());
                                    dr.Close();
                                    cmd.Dispose();
                                    // сохраняем нужное значение что бы потом не проводить поиск.
                                    sql = "update DEPARTMENT set IMPORTDUE='" + importDUE + "',IMPORTISN=" + importISN.ToString() + " WHERE DUE='" + xmlDUE + "'";
                                    cmd = new OleDbCommand(sql, Connection);
                                    cmd.ExecuteNonQuery();
                                    cmd.Dispose();
                                    return importDUE;
                                }
                            }
                        }
                        // ---------------------- Поиск только по названию ----------------------------------------------
                        sql = "select DUE, ISN_NODE from " + sqlprefix + "DEPARTMENT WHERE " +
                            "(UPPER(CLASSIF_NAME) like '" + name.ToUpper() + "%') " +
                            " AND (DUE like '" + topDepartment + "%')  ";
                        cmd = new OleDbCommand(sql, SyncConnection);
                        dr = cmd.ExecuteReader();
                        if (dr.Read())
                        {
                            importDUE = dr.GetString(0);
                            importISN = Int32.Parse(dr.GetValue(1).ToString());
                            dr.Close();
                            cmd.Dispose();
                            // сохраняем нужное значение что бы потом не проводить поиск.
                            sql = "update DEPARTMENT set IMPORTDUE='" + importDUE + "',IMPORTISN=" + importISN.ToString() + " WHERE DUE='" + xmlDUE + "'";
                            cmd = new OleDbCommand(sql, Connection);
                            cmd.ExecuteNonQuery();
                            cmd.Dispose();
                            return importDUE;
                        }

                        Int32 pisn = 0;
                        if (parentname != "")
                        {
                            sql = "SELECT IMPORTISN FROM DEPARTMENT WHERE DUE='" + GetParentDue(xmlDUE) + "'"; // Ищем родителя в таблице
                            cmd = new OleDbCommand(sql, Connection);
                            t_isn = cmd.ExecuteScalar();
                            cmd.Dispose();
                            if ((t_isn != null) && (t_isn != DBNull.Value) && ((Int32)t_isn != 0))
                            {
                                pisn = (Int32)t_isn;
                            }
                        }
                        if (pisn == 0) // Если родитель в таблице не найден то ищем по инени родителя
                        {
                            sql = "SELECT ISN_NODE FROM " + sqlprefix + "DEPARTMENT WHERE (DUE like '" +
                                topDepartment + "%') AND " +
                                "(UPPER(CLASSIF_NAME) = '" + parentname.ToUpper() + "') ";
                            cmd = new OleDbCommand(sql, SyncConnection);
                            t_isn = cmd.ExecuteScalar();
                            cmd.Dispose();
                            if ((t_isn != null) && (t_isn != DBNull.Value) && (Int32.Parse(t_isn.ToString()) != 0))
                            {
                                pisn = Int32.Parse(t_isn.ToString());
                            }
                        }
                        if (pisn == 0) // Если родитель не найден то родителем будет папка по умолчанию
                        {
                            pisn = topDepartmentISN;
                        }
                        ImportDepartment.ImportDepartment.Init(SyncConnection, null, sqlprefix);
                        isndue = ImportDepartment.ImportDepartment.AppendDepartment(pisn,
                                 ((ExportDepartment.SelectNodes("IS_NODE").Count > 0) ? Int16.Parse(ExportDepartment.SelectSingleNode("IS_NODE").InnerText) : (Int16)1),
                                 name,
                                 ((ExportDepartment.SelectNodes("SURNAME").Count > 0) ? ExportDepartment.SelectSingleNode("SURNAME").InnerText : ""),
                                 ((ExportDepartment.SelectNodes("DUTY").Count > 0) ? ExportDepartment.SelectSingleNode("DUTY").InnerText : ""),
                                 ((ExportDepartment.SelectNodes("DELETED").Count > 0) ? Int16.Parse(ExportDepartment.SelectSingleNode("DELETED").InnerText) : (Int16)1),
                                 ((ExportDepartment.SelectNodes("DEPARTMENT_INDEX").Count > 0) ? ExportDepartment.SelectSingleNode("DEPARTMENT_INDEX").InnerText : ""),
                                 ((ExportDepartment.SelectNodes("POST_H").Count > 0) ? Int16.Parse(ExportDepartment.SelectSingleNode("POST_H").InnerText) : (Int16)0),
                                 ((ExportDepartment.SelectNodes("NOTE").Count > 0) ? ExportDepartment.SelectSingleNode("NOTE").InnerText : ""));
                        importDUE = isndue.Split('#')[1];
                        importISN = Int32.Parse(isndue.Split('#')[0]);

                        // сохраняем нужное значение что бы потом не проводить поиск.
                        sql = "update DEPARTMENT set IMPORTDUE='" + importDUE + "',IMPORTISN=" + importISN.ToString() + " WHERE DUE='" + xmlDUE + "'";
                        cmd = new OleDbCommand(sql, Connection);
                        cmd.ExecuteNonQuery();
                        cmd.Dispose();
                        return importDUE;
                    }
                case TAnalys.bytable:
                    if (checksynctable)
                    {
                        sql = "select IMPORTDUE,IMPORTISN from DEPARTMENT where DUE = '" + xmlDUE + "'";
                        cmd = new OleDbCommand(sql, Connection);
                        dr = cmd.ExecuteReader();
                    }
                    if (dr.Read() && (!dr.IsDBNull(0)))
                    {
                        importDUE = dr.GetString(0);
                        dr.Close();
                        return importDUE;
                    }
                    else return topDepartment;
                default:
                    return topDepartment;
            }
        }

        /// <summary>
        /// метод для синхронизации подразделений
        ///</summary>
        public Int32 syncDepartmentISN(XmlNode ExportDepartment)
        {
            string sql;
            OleDbCommand cmd;
            OleDbDataReader dr = null;
            string parentname = "";
            string name = "";
            string importDUE;
            Int32 xmlISN;
            string xmlDUE;
            Int32 importISN;
            object t_isn;
            string isndue = "";
            xmlISN = Int32.Parse(ExportDepartment.SelectSingleNode("@ISN").InnerText);
            xmlDUE = ExportDepartment.SelectSingleNode("@DUE").InnerText;
            switch (analyz)
            {
                case TAnalys.onetoone:
                    return xmlISN;
                case TAnalys.byname:
                    if (checksynctable)
                    {
                        sql = "select IMPORTDUE,IMPORTISN from DEPARTMENT where ISN_NODE = '" + xmlISN + "'";
                        cmd = new OleDbCommand(sql, Connection);
                        dr = cmd.ExecuteReader();
                    }
                    if (checksynctable && dr.Read() && (!dr.IsDBNull(1)))
                    {
                        importISN = dr.GetInt32(1);
                        dr.Close();
                        //cmd.Dispose();
                        return importISN;
                    }
                    else
                    {
                        if (SyncConnection == null) throw new Exception("Этот вид синхронизации предполагает указание соединения для синхронизации!");
                        if (ExportDepartment == null) throw new Exception("Не задано название департамента. Поиск не возможен!");
                        name = ExportDepartment.SelectSingleNode("CLASSIF_NAME").InnerText;
                        if (ExportDepartment.SelectNodes("SURNAME").Count > 0) // если это возможно ищем по фамилии т.к. должность может быть писаться по разному
                        {
                            name = ExportDepartment.SelectSingleNode("SURNAME").InnerText;
                        }
                        // ---------------------- Поиск при наличии родителя ----------------------------------------------
                        if (ExportDepartment.SelectNodes("PARENT/CLASSIF_NAME").Count > 0) // если это возможно ищем по фамилии т.к. должность может быть писаться по разному
                        {
                            parentname = ExportDepartment.SelectSingleNode("PARENT/CLASSIF_NAME").InnerText;
                            if (parentname != "")
                            {
                                sql = "select D1.DUE, D1.ISN_NODE from " + sqlprefix + "DEPARTMENT D1, DEPARTMENT D2 WHERE " +
                                      "(UPPER(D1.CLASSIF_NAME) like '" + name.ToUpper() + "%') " +
                                      "and (D1.ISN_HIGH_NODE=D2.ISN_NODE) AND (D1.DUE like '" + topDepartment +
                                      "%') AND (D2.DUE like '" + topDepartment + "%') AND " +
                                      "(UPPER(D2.CLASSIF_NAME) = '" + parentname.ToUpper() + "') ";
                                cmd = new OleDbCommand(sql, SyncConnection);
                                dr = cmd.ExecuteReader();

                                if (dr.Read())
                                {
                                    importDUE = dr.GetString(0);
                                    importISN = Int32.Parse(dr.GetValue(1).ToString());
                                    dr.Close();
                                    cmd.Dispose();
                                    // сохраняем нужное значение что бы потом не проводить поиск.
                                    sql = "update DEPARTMENT set IMPORTDUE='" + importDUE + "',IMPORTISN=" + importISN.ToString() + " WHERE ISN_NODE=" + xmlISN.ToString();
                                    cmd = new OleDbCommand(sql, Connection);
                                    cmd.ExecuteNonQuery();
                                    cmd.Dispose();
                                    return importISN;
                                }
                            }
                        }
                        // ---------------------- Поиск только по названию ----------------------------------------------
                        sql = "select DUE, ISN_NODE from " + sqlprefix + "DEPARTMENT WHERE " +
                            "(UPPER(CLASSIF_NAME) = '" + name.ToUpper() + "') " +
                            " AND (DUE like '" + topDepartment + "%')  ";
                        cmd = new OleDbCommand(sql, SyncConnection);
                        dr = cmd.ExecuteReader();
                        if (dr.Read())
                        {
                            importDUE = dr.GetString(0);
                            importISN = Int32.Parse(dr.GetValue(1).ToString());
                            dr.Close();
                            cmd.Dispose();
                            // сохраняем нужное значение что бы потом не проводить поиск.
                            sql = "update DEPARTMENT set IMPORTDUE='" + importDUE + "',IMPORTISN=" + importISN.ToString() + " WHERE ISN_NODE=" + xmlISN.ToString();
                            cmd = new OleDbCommand(sql, Connection);
                            cmd.ExecuteNonQuery();
                            cmd.Dispose();
                            return importISN;
                        }

                        Int32 pisn = 0;
                        if (parentname != "")
                        {
                            sql = "SELECT IMPORTISN FROM DEPARTMENT WHERE DUE='" + GetParentDue(xmlDUE) + "'"; // Ищем родителя в таблице
                            cmd = new OleDbCommand(sql, Connection);
                            t_isn = cmd.ExecuteScalar();
                            cmd.Dispose();
                            if ((t_isn != null) && (t_isn != DBNull.Value) && (Int32.Parse(t_isn.ToString()) != 0))
                            {
                                pisn = Int32.Parse(t_isn.ToString());
                            }
                        }
                        if (pisn == 0) // Если родитель в таблице не найден то ищем по инени родителя
                        {
                            sql = "SELECT ISN_NODE FROM " + sqlprefix + "DEPARTMENT WHERE (DUE like '" +
                                topDepartment + "%') AND " +
                                "(UPPER(CLASSIF_NAME) = '" + parentname.ToUpper() + "') ";
                            cmd = new OleDbCommand(sql, SyncConnection);
                            t_isn = cmd.ExecuteScalar();
                            cmd.Dispose();
                            if ((t_isn != null) && (t_isn != DBNull.Value) && (Int32.Parse(t_isn.ToString()) != 0))
                            {
                                pisn = Int32.Parse(t_isn.ToString());
                            }
                        }
                        if (pisn == 0) // Если родитель не найден то родителем будет папка по умолчанию
                        {
                            pisn = topDepartmentISN;
                        }
                        ImportDepartment.ImportDepartment.Init(SyncConnection, null, sqlprefix);
                        isndue = ImportDepartment.ImportDepartment.AppendDepartment(pisn,
                                 ((ExportDepartment.SelectNodes("IS_NODE").Count > 0) ? Int16.Parse(ExportDepartment.SelectSingleNode("IS_NODE").InnerText) : (Int16)1),
                                 name,
                                 ((ExportDepartment.SelectNodes("SURNAME").Count > 0) ? ExportDepartment.SelectSingleNode("SURNAME").InnerText : ""),
                                 ((ExportDepartment.SelectNodes("DUTY").Count > 0) ? ExportDepartment.SelectSingleNode("DUTY").InnerText : ""),
                                 ((ExportDepartment.SelectNodes("DELETED").Count > 0) ? Int16.Parse(ExportDepartment.SelectSingleNode("DELETED").InnerText) : (Int16)1),
                                 ((ExportDepartment.SelectNodes("DEPARTMENT_INDEX").Count > 0) ? ExportDepartment.SelectSingleNode("DEPARTMENT_INDEX").InnerText : ""),
                                 ((ExportDepartment.SelectNodes("POST_H").Count > 0) ? Int16.Parse(ExportDepartment.SelectSingleNode("POST_H").InnerText) : (Int16)0),
                                 ((ExportDepartment.SelectNodes("NOTE").Count > 0) ? ExportDepartment.SelectSingleNode("NOTE").InnerText : ""));
                        importDUE = isndue.Split('#')[1];
                        importISN = Int32.Parse(isndue.Split('#')[0]);

                        // сохраняем нужное значение что бы потом не проводить поиск.
                        sql = "update DEPARTMENT set IMPORTDUE='" + importDUE + "',IMPORTISN=" + importISN.ToString() + " WHERE ISN_NODE=" + xmlISN.ToString();
                        cmd = new OleDbCommand(sql, Connection);
                        cmd.ExecuteNonQuery();
                        cmd.Dispose();
                        return importISN;
                    }
                case TAnalys.bytable:
                    if (checksynctable)
                    {
                        sql = "select IMPORTDUE,IMPORTISN from DEPARTMENT where DUE = '" + xmlISN + "'";
                        cmd = new OleDbCommand(sql, Connection);
                        dr = cmd.ExecuteReader();
                    }
                    if (dr.Read() && (!dr.IsDBNull(1)))
                    {
                        importISN = dr.GetInt32(1);
                        dr.Close();
                        return importISN;
                    }
                    else return topDepartmentISN;
                default:
                    return topDepartmentISN;
            }
        }
        /// <summary>
        /// метод для вычисления родительского кода ДЬЮИ
        ///</summary>
        private string GetParentDue(string due)
        {
            string[] _chield = due.Split('.');
            string _parent = "";
            for (int i = 0; i < _chield.Length - 1; i++)
            {
                _parent += _chield[i] + ".";
            }
            return _parent;
        }

        public void SaveDocgroupNewID(Int32 SearchIsn, Int32 NewIsn, String NewDue)
        {
            String sql = "update docgroup set IMPORTISN=" + NewIsn.ToString() + ", IMPORTDUE='" + NewDue + "' WHERE ISN_NODE=" + SearchIsn.ToString();
            OleDbCommand cmd = new OleDbCommand(sql, Connection);
            cmd.ExecuteNonQuery();
        }
        public void SaveDepartmentNewID(Int32 SearchIsn, Int32 NewIsn, String NewDue)
        {
            String sql = "update DEPARTMENT set IMPORTISN=" + NewIsn.ToString() + ", IMPORTDUE='" + NewDue + "' WHERE ISN_NODE=" + SearchIsn.ToString();
            OleDbCommand cmd = new OleDbCommand(sql, Connection);
            cmd.ExecuteNonQuery();
        }
        public void SaveRubricNewID(Int32 SearchIsn, Int32 NewIsn, String NewDue)
        {
            String sql = "update RUBRIC_CL set IMPORTISN=" + NewIsn.ToString() + ", IMPORTDUE='" + NewDue + "' WHERE ISN_NODE=" + SearchIsn.ToString();
            OleDbCommand cmd = new OleDbCommand(sql, Connection);
            cmd.ExecuteNonQuery();
        }

        public void InsertLink(string name, string para_name, out string isn, out string para_isn)
        {
            try
            {
                OleDbCommand ExecCmd = new OleDbCommand();

                ExecCmd.Connection = SyncConnection;
                ExecCmd.CommandType = CommandType.StoredProcedure;
                ExecCmd.CommandText = "GET_SEQ";
                ExecCmd.Parameters.Add("asname", OleDbType.VarChar);
                ExecCmd.Parameters["asname"].Value = "SEQ_CLASSIF";
                ExecCmd.Parameters.Add("airet", OleDbType.Integer);
                ExecCmd.Parameters["airet"].Direction = ParameterDirection.Output;
                ExecCmd.ExecuteNonQuery();
                isn = ExecCmd.Parameters["airet"].Value.ToString();
                if (para_name != name && para_name != "")
                {
                    ExecCmd.ExecuteNonQuery();
                    para_isn = ExecCmd.Parameters["airet"].Value.ToString();
                }
                else para_isn = "0";
                ExecCmd.Dispose();
                string sql = "INSERT INTO " + sqlprefix + "LINK_CL (ISN_LCLASSIF,CLASSIF_NAME,ISN_PARE_LINK) values(" + isn.ToString() + ", '" + name + "', ";
                if (para_isn == "0")
                {
                    sql += isn.ToString() + ")";
                    ExecCmd = new OleDbCommand(sql, SyncConnection);
                    ExecCmd.ExecuteNonQuery();
                    ExecCmd.Dispose();
                }
                else
                {
                    sql += para_isn.ToString() + ")";
                    ExecCmd = new OleDbCommand(sql, SyncConnection);
                    ExecCmd.ExecuteNonQuery();
                    ExecCmd.Dispose();
                    sql = "INSERT INTO " + sqlprefix + "LINK_CL (ISN_LCLASSIF,CLASSIF_NAME,ISN_PARE_LINK) values("
                        + para_isn.ToString() + ", '" + para_name + "', " + isn.ToString() + ")";
                    ExecCmd = new OleDbCommand(sql, SyncConnection);
                    ExecCmd.ExecuteNonQuery();
                    ExecCmd.Dispose();
                }
            }
            catch (Exception ex)
            {
                throw new Exception("InsertLink || " + ex.Message);
            }
        }
    }
    
}
